USE [QuanLyHopDong]
GO

/****** Object:  View [dbo].[ThongKeHopDongChuaThanhToanView]    Script Date: 10/8/2014 12:24:55 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


ALTER view [dbo].[ThongKeHopDongChuaThanhToanView]
as
select 
	HD.Id, HD.SoHopDong, HD.TenHopDong, HD.GiaTriHopDongChuaVAT, HD.TongGiaTriHopDong,
	DV.Ten as TenDonVi,
	QTNB.GiaTriQuyetToanNoiBo,
	QT.GiaTriQuyetToan,
	QT.NgayQuyetToan,
	CTT.SoTien as TongSoTienChuaThanhToan,
	DTT.SoTien as TongSoTienDaThanhToan,
	SoTienConLai =
	case
		when (QT.GiaTriQuyetToan is not null) then (QT.GiaTriQuyetToan - ISNULL(DTT.SoTien, 0))
		else ISNULL(CTT.SoTien, 0)
	end 
from
	HopDong HD
	left outer join DonVi DV on HD.DonViId = DV.Id
	left outer join QuyetToan QT on QT.HopDongId = HD.Id
	left outer join GiaTriQuyetToanNoiBoView QTNB on QTNB.HopDongId = HD.Id
	left outer join ChuaThanhToanView CTT on CTT.HopDongId = HD.Id
	left outer join DaThanhToanView DTT on DTT.HopDongId = HD.Id
where
	((QT.GiaTriQuyetToan is null) and (ISNULL(CTT.SoTien, 0) > 0)) OR
	((QT.GiaTriQuyetToan is not null) and (QT.GiaTriQuyetToan - ISNULL(DTT.SoTien, 0) > 0))


GO


